﻿using System;
using System.Collections.Generic;
using System.Data;
using br.com.SistemaChamados.Enum;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados.QueryManager.Core;
using br.com.SistemaChamados.QueryManager.Core.Behavior;

namespace br.com.SistemaChamados.DAO
{
    public class PlaceDAO : GenericDao<Place>
    {
        private static string SQL_UPDATE_PLACE    = "UPDATE TBL_PLACE SET TBL_PLACEZIP = @placezip, TBL_PLACEADDRESS = @placeaddress, TBL_PLACECOMPLEMENT = @placecomplement, TBL_PLACENUMBER = @placenumber, TBL_CITYID = @cityid, tbl_PLACEDESCRIPTION = @description WHERE  TBL_PLACEID = @placeid";
        private static string SQL_DELETE_PLACE    = " DELETE FROM TBL_PLACE WHERE TBL_PLACEID = @id";
        private static string SQL_INSERT_PLACE    = "INSERT INTO TBL_PLACE (TBL_PLACEZIP, TBL_PLACEADDRESS, TBL_PLACECOMPLEMENT, TBL_PLACENUMBER, TBL_CITYID, TBL_PLACEDESCRIPTION) VALUES (@placezip, @placeaddress, @placecomplement, @placenumber, @cityid, @description)";
        private static string SQL_GET_PLACES_CITY = " SELECT * FROM TBL_PLACE WHERE TBL_CITYID=@cityid";

        public static EnumCRUDState UpdatePlace(Place place)
        {
            EnumCRUDState resultPlace = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_UPDATE_PLACE, conn);
                command.Parameters.Add(Mapped.Parameter("@placeid", place.Id));
                command.Parameters.Add(Mapped.Parameter("@placezip", place.ZipCode));
                command.Parameters.Add(Mapped.Parameter("@placeaddress", place.Address));
                command.Parameters.Add(Mapped.Parameter("@placecomplement", place.Complement));
                command.Parameters.Add(Mapped.Parameter("@placenumber", place.Number));
                command.Parameters.Add(Mapped.Parameter("@cityid", place.City.Id));
                command.Parameters.Add(Mapped.Parameter("@description", place.Description));
                command.ExecuteNonQuery();
                resultPlace = EnumCRUDState.SQL_SUCCESS;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultPlace;
        }

        public static EnumCRUDState DeletePlace(Place place)
        {
            EnumCRUDState resultPlace = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_PLACE, conn);
                command.Parameters.Add(Mapped.Parameter("@id", place.Id));
                command.ExecuteNonQuery();
                resultPlace = EnumCRUDState.SQL_SUCCESS;
            }
            catch (Exception ex)
            {
                resultPlace = EnumCRUDState.SQL_ERROR;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultPlace;
        }

        public static EnumCRUDState InsertPlace(Place place)
        {
            EnumCRUDState resultPlace = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_INSERT_PLACE, conn);
                command.Parameters.Add(Mapped.Parameter("@placezip", place.ZipCode));
                command.Parameters.Add(Mapped.Parameter("@placeaddress", place.Address));
                command.Parameters.Add(Mapped.Parameter("@placecomplement", place.Complement));
                command.Parameters.Add(Mapped.Parameter("@placenumber", place.Number));
                command.Parameters.Add(Mapped.Parameter("@cityid", place.City.Id));
                command.Parameters.Add(Mapped.Parameter("@description", place.Description));
                command.ExecuteNonQuery();
                resultPlace = EnumCRUDState.SQL_SUCCESS;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultPlace;
        }
        
        public static Place GetPlace(int placeId)
        {
            Place resultPlace = null;
            try
            {
                resultPlace = (Place)QueryManagerPerformer.Select(typeof(Place), "Id", placeId, ConnectionBehavior.CONNECTION_BEHAVIOR_CLOSE_AFTER_OPERATION);               
            }
            catch (Exception ex)
            {
                resultPlace = null;
                throw ex;
            }
            return resultPlace;
        }

        public static List<Place> GetPlaces()
        {
            List<Place> list = new List<Place>();
            IDbConnection conn = null;
            try
            {
                list = QueryManagerPerformer.Select(typeof(Place), ConnectionBehavior.CONNECTION_BEHAVIOR_CLOSE_AFTER_OPERATION)
                        .ConvertAll(convertObject);
                return list;
            }
            catch (Exception ex)
            {
                list = null;
                return list;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();                
            }
        }

        public static List<Place> GetPlacesByCity(City city)
        {
            List<Place> list = new List<Place>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            Place resultPlace = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_PLACES_CITY, conn);
                command.Parameters.Add(Mapped.Parameter("@cityid", city.Id));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    resultPlace = new Place();
                    city = extractCityFromResultSet(reader);
                    resultPlace.Id = Convert.ToInt32(reader["TBL_PLACEID"]);
                    resultPlace.Address = Convert.ToString(reader["TBL_PLACEADDRESS"]);                                        
                    resultPlace.Description = reader["TBL_PLACEDESCRIPTION"].ToString();
                    resultPlace.City = city;
                    list.Add(resultPlace);
                }
                return list;
            }
            catch (Exception ex)
            {
                list = null;
                return list;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

        }

        private static City extractCityFromResultSet(IDataReader reader)
        {
            City city = new City();
            city.Id = Convert.ToInt32(reader["TBL_CITYID"]);
            return city;
        }
    }
}